{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import necessary Libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from matplotlib import pyplot as plt\n",
    "import ast\n",
    "import json\n",
    "import argparse\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\86133\\AppData\\Local\\Temp\\ipykernel_17476\\3222270942.py:4: DtypeWarning: Columns (10) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  meta=pd.read_csv(\"data/movies_metadata.csv\")\n"
     ]
    }
   ],
   "source": [
    "# Read Datasets\n",
    "credit_data=pd.read_csv(\"data/credits.csv\")\n",
    "# Read Meta dataset\n",
    "meta=pd.read_csv(\"data/movies_metadata.csv\")\n",
    "keywords = pd.read_csv('data/keywords.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a function which convert to a list of genres\n",
    "def make_genresList(x):\n",
    "    gen = []\n",
    "    st = \" \"\n",
    "    for i in x:\n",
    "        if i.get('name') == 'Science Fiction':\n",
    "            scifi = 'Sci-Fi'\n",
    "            gen.append(scifi)\n",
    "        else:\n",
    "            gen.append(i.get('name'))\n",
    "    if gen == []:\n",
    "        return np.NaN\n",
    "    else:\n",
    "        return (st.join(gen))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a function which extract first actor from \"cast\" column\n",
    "def get_actor1(x):\n",
    "    casts=[]\n",
    "    for i in x:\n",
    "        casts.append(i.get(\"name\"))\n",
    "        if casts==[]:\n",
    "            return np.Nan\n",
    "        else:\n",
    "            return (casts[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a function which extract second actor from \"cast\" column\n",
    "def get_actor2(x):\n",
    "    casts = []\n",
    "    for i in x:\n",
    "        casts.append(i.get('name'))\n",
    "    if casts == [] or len(casts)<=1:\n",
    "        return np.NaN\n",
    "    else:\n",
    "        return (casts[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a function which extract third actor from \"cast\" column\n",
    "def get_actor3(x):\n",
    "    casts = []\n",
    "    for i in x:\n",
    "        casts.append(i.get('name'))\n",
    "    if casts == [] or len(casts)<=2:\n",
    "        return np.NaN\n",
    "    else:\n",
    "        return (casts[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a function which extract Director name from \"crew\" column\n",
    "def get_directors(x):\n",
    "    dt = []\n",
    "    st = \" \"\n",
    "    for i in x:\n",
    "        if i.get('job') == 'Director':\n",
    "            dt.append(i.get('name'))\n",
    "    if dt == []:\n",
    "        return np.NaN\n",
    "    else:\n",
    "        return (st.join(dt))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean(credit_data,meta,keywords):\n",
    "    # Extract years from \" release_date \"\n",
    "    meta[\"release_date\"]=pd.to_datetime(meta[\"release_date\"],errors=\"coerce\")\n",
    "    meta['year'] = meta['release_date'].dt.year\n",
    "    meta[\"year\"].value_counts().sort_index()\n",
    "    # In meta dataset Extract 'genres','id','title','year' of 2017 movies\n",
    "    new_meta=meta.loc[meta.year==2017,['genres','id','title','year']]\n",
    "    # Convert Id column datatype to int\n",
    "    new_meta[\"id\"]=new_meta[\"id\"].astype(int)\n",
    "    data = pd.merge(new_meta, credit_data, on='id')\n",
    "    pd.set_option('display.max_colwidth', 75)\n",
    "    data[\"genres\"]=data[\"genres\"].map(lambda x : ast.literal_eval(x))\n",
    "    data[\"cast\"]=data[\"cast\"].map(lambda x : ast.literal_eval(x))\n",
    "    data[\"crew\"]=data[\"crew\"].map(lambda x : ast.literal_eval(x))\n",
    "    data['genres_list'] = data['genres'].map(lambda x: make_genresList(x))\n",
    "    data['actor_1_name'] = data['cast'].map(lambda x: get_actor1(x))\n",
    "    data['actor_2_name'] = data['cast'].map(lambda x: get_actor2(x))\n",
    "    data['actor_3_name'] = data['cast'].map(lambda x: get_actor3(x))\n",
    "    data['director_name'] = data['crew'].map(lambda x: get_directors(x))\n",
    "    movies_data=data.loc[:,['actor_1_name','actor_2_name', 'actor_3_name', 'director_name',\"genres_list\",'title']]\n",
    "    movies_data=movies_data.dropna(how=\"any\")\n",
    "    # Rename columns\n",
    "    movies_data=movies_data.rename(columns={'genres_list':'genres'})\n",
    "    movies_data=movies_data.rename(columns={'title':'movie_title'})\n",
    "    movies_data[\"movie_title\"]=movies_data[\"movie_title\"].str.lower()\n",
    "    # 使用rename()方法更改列名\n",
    "    movies_data = movies_data.rename(columns={'movie_title': 'title'})\n",
    "    meta[\"title\"]=meta[\"title\"].str.lower()\n",
    "    movies_data[\"comb\"]=movies_data[\"actor_1_name\"]+' '+movies_data[\"actor_2_name\"]+\" \"+ movies_data[\"actor_3_name\"]+' ' + movies_data[\"director_name\"]\n",
    "    meta = meta[['id', 'title', 'overview','popularity','runtime','vote_average','vote_count']]\n",
    "    movies_data = movies_data[['comb','title']]\n",
    "    movie = pd.merge(meta,movies_data,left_on='title',right_on='title',how ='left')\n",
    "    # 替换NaN为''\n",
    "    keywords['keywords'] = keywords['keywords'].fillna('')\n",
    "\n",
    "    # 定义一个函数来提取关键词的name\n",
    "    def extract_keywords(keywords_str):\n",
    "        try:\n",
    "            keywords_list = ast.literal_eval(keywords_str)\n",
    "            keywords_names = ' '.join([keyword['name'] for keyword in keywords_list])\n",
    "            return keywords_names\n",
    "        except (ValueError, SyntaxError):\n",
    "            return ''\n",
    "\n",
    "    # 应用函数提取关键词的name\n",
    "    keywords['keywords_extracted'] = keywords['keywords'].apply(extract_keywords)\n",
    "    # 将标题和概述用空格隔开并存储到一个数组（列表）中\n",
    "    keywords['keywords'] = keywords['keywords_extracted']\n",
    "    # 替换NaN为''\n",
    "    keywords['keywords'] = keywords['keywords'].fillna('')\n",
    "\n",
    "    # 将id列转换为整数类型，以确保合并时的一致性\n",
    "    movie['id'] = pd.to_numeric(movie['id'], errors='coerce')\n",
    "    keywords['id'] = pd.to_numeric(keywords['id'], errors='coerce')\n",
    "    # 替换NaN为''\n",
    "    movie['title'] = movie['title'].fillna('')\n",
    "    movie['overview'] = movie['overview'].fillna('')\n",
    "    # 删除无效的id行\n",
    "    # 删除重复的id，保留第一个\n",
    "    movie = movie.drop_duplicates(subset='id', keep='first')\n",
    "    # 删除重复的id，保留第一个\n",
    "    keywords = keywords.drop_duplicates(subset='id', keep='first')\n",
    "\n",
    "    # 合并两个DataFrame\n",
    "    merged_data = pd.merge(movie, keywords,left_on='id',right_on='id',how ='left')\n",
    "    # 清理 NaN 值，将其替换为空字符串\n",
    "    merged_data['keywords'] = merged_data['keywords'].fillna(' ')\n",
    "    # 清理 NaN 值，将其替换为空字符串\n",
    "    merged_data['comb'] = merged_data['comb'].fillna(' ')\n",
    "    merged_data['title'] = merged_data['title'].fillna(' ')\n",
    "    merged_data['overview'] = merged_data['overview'].fillna(' ')\n",
    "    # 添加新列 'ad' 并将其值设置为 0\n",
    "    merged_data['ad'] = 1.0\n",
    "    merged_data.to_csv(\"updata/merge_data.csv\")\n",
    "\n",
    "\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean1(credit_data,meta,keywords):\n",
    "    # Extract years from \" release_date \"\n",
    "    meta[\"release_date\"]=pd.to_datetime(meta[\"release_date\"],errors=\"coerce\")\n",
    "    meta['year'] = meta['release_date'].dt.year\n",
    "    meta[\"year\"].value_counts().sort_index()\n",
    "    # In meta dataset Extract 'genres','id','title','year' of 2017 movies\n",
    "    new_meta=meta.loc[meta.year==2017,['genres','id','title','year']]\n",
    "    # Convert Id column datatype to int\n",
    "    new_meta[\"id\"]=new_meta[\"id\"].astype(int)\n",
    "    data = pd.merge(new_meta, credit_data, on='id')\n",
    "    pd.set_option('display.max_colwidth', 75)\n",
    "    data[\"genres\"]=data[\"genres\"].map(lambda x : ast.literal_eval(x))\n",
    "    data[\"cast\"]=data[\"cast\"].map(lambda x : ast.literal_eval(x))\n",
    "    data[\"crew\"]=data[\"crew\"].map(lambda x : ast.literal_eval(x))\n",
    "    data['genres_list'] = data['genres'].map(lambda x: make_genresList(x))\n",
    "    data['actor_1_name'] = data['cast'].map(lambda x: get_actor1(x))\n",
    "    data['actor_2_name'] = data['cast'].map(lambda x: get_actor2(x))\n",
    "    data['actor_3_name'] = data['cast'].map(lambda x: get_actor3(x))\n",
    "    data['director_name'] = data['crew'].map(lambda x: get_directors(x))\n",
    "    movies_data=data.loc[:,['actor_1_name','actor_2_name', 'actor_3_name', 'director_name',\"genres_list\",'title']]\n",
    "    movies_data=movies_data.dropna(how=\"any\")\n",
    "    # Rename columns\n",
    "    movies_data=movies_data.rename(columns={'genres_list':'genres'})\n",
    "    movies_data=movies_data.rename(columns={'title':'movie_title'})\n",
    "    movies_data[\"movie_title\"]=movies_data[\"movie_title\"].str.lower()\n",
    "    # 使用rename()方法更改列名\n",
    "    movies_data = movies_data.rename(columns={'movie_title': 'title'})\n",
    "    meta[\"title\"]=meta[\"title\"].str.lower()\n",
    "    movies_data[\"comb\"]=movies_data[\"actor_1_name\"]+' '+movies_data[\"actor_2_name\"]+\" \"+ movies_data[\"actor_3_name\"]+' ' + movies_data[\"director_name\"]\n",
    "    meta = meta[['id', 'title', 'overview','popularity','runtime','vote_average','vote_count']]\n",
    "    movies_data = movies_data[['comb','title']]\n",
    "    movie = pd.merge(meta,movies_data,left_on='title',right_on='title',how ='left')\n",
    "    # 替换NaN为''\n",
    "    keywords['keywords'] = keywords['keywords'].fillna('')\n",
    "    print(keywords['keywords'])\n",
    "    # 定义一个函数来提取关键词的name\n",
    "    def extract_keywords(keywords_str):\n",
    "        try:\n",
    "            keywords_list = ast.literal_eval(keywords_str)\n",
    "            keywords_names = ' '.join([keyword['name'] for keyword in keywords_list])\n",
    "            print(keywords_names)\n",
    "            return keywords_names\n",
    "        except (ValueError, SyntaxError):\n",
    "            return ''\n",
    "    print(keywords['keywords'])\n",
    "    # 应用函数提取关键词的name\n",
    "    keywords['keywords_extracted'] = keywords['keywords'].apply(extract_keywords)\n",
    "    print(keywords['keywords_extracted'] )\n",
    "    # 将标题和概述用空格隔开并存储到一个数组（列表）中\n",
    "    keywords['keywords'] = keywords['keywords_extracted']\n",
    "    # 替换NaN为''\n",
    "    keywords['keywords'] = keywords['keywords'].fillna('')\n",
    "\n",
    "    # 将id列转换为整数类型，以确保合并时的一致性\n",
    "    movie['id'] = pd.to_numeric(movie['id'], errors='coerce')\n",
    "    keywords['id'] = pd.to_numeric(keywords['id'], errors='coerce')\n",
    "    # 替换NaN为''\n",
    "    movie['title'] = movie['title'].fillna('')\n",
    "    movie['overview'] = movie['overview'].fillna('')\n",
    "    # 删除无效的id行\n",
    "    # 删除重复的id，保留第一个\n",
    "    movie = movie.drop_duplicates(subset='id', keep='first')\n",
    "    # 删除重复的id，保留第一个\n",
    "    keywords = keywords.drop_duplicates(subset='id', keep='first')\n",
    "\n",
    "    # 合并两个DataFrame\n",
    "    merged_data = pd.merge(movie, keywords,left_on='id',right_on='id',how ='left')\n",
    "    # 清理 NaN 值，将其替换为空字符串\n",
    "    merged_data['keywords'] = merged_data['keywords'].fillna(' ')\n",
    "    # 清理 NaN 值，将其替换为空字符串\n",
    "    merged_data['comb'] = merged_data['comb'].fillna(' ')\n",
    "    merged_data['title'] = merged_data['title'].fillna(' ')\n",
    "    merged_data['overview'] = merged_data['overview'].fillna(' ')\n",
    "    # 添加新列 'ad' 并将其值设置为 0\n",
    "    merged_data['ad'] = 1.0\n",
    "    # 目标 CSV 文件路径\n",
    "    csv_file_path = \"updata/merge_data2.csv\"\n",
    "\n",
    "    # 检查文件是否存在\n",
    "    file_exists = os.path.isfile(csv_file_path)\n",
    "\n",
    "    # 追加数据到 CSV 文件\n",
    "    merged_data.to_csv(csv_file_path, mode='a', index=False, header=not file_exists)\n",
    "\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean(credit_data,meta,keywords)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_json_from_string(input_string):\n",
    "    try:\n",
    "        # 尝试将整个字符串解析为 JSON 对象\n",
    "        json_data = json.loads(input_string)\n",
    "        return json_data\n",
    "    except json.JSONDecodeError as e:\n",
    "        print(f\"Error decoding JSON: {e}\")\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    [{\"id\": 931, \"name\": \"jealousy\"}]\n",
      "Name: keywords, dtype: object\n",
      "jealousy\n",
      "0    jealousy\n",
      "Name: keywords_extracted, dtype: object\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "\n",
    "def json_to_dataframes(json_data):\n",
    "    \"\"\"\n",
    "    将 JSON 数据转换为多个 Pandas DataFrame，并创建一个 credits DataFrame。\n",
    "    \"\"\"\n",
    "    # 解析 JSON 数据\n",
    "    data = json.loads(json_data)\n",
    "\n",
    "    # 提取演员信息并转换为 DataFrame\n",
    "    actors_df = pd.DataFrame(data.get('actors', []))\n",
    "\n",
    "    # 提取电影的基本信息并转换为 DataFrame\n",
    "    movie_info = data.get('movie', {})\n",
    "    \n",
    "    # 将 genreHubs 移动到 movie 中并重命名为 genres\n",
    "    genre_hubs = data.pop(\"genreHubs\", [])\n",
    "    movie_info['genres'] = genre_hubs\n",
    "    \n",
    "  # 定义 movies_df 需要的所有列\n",
    "    movie_columns = [\n",
    "        'id', 'adult', 'belongs_to_collection', 'budget', 'genres', 'homepage', 'originalLanguage',\n",
    "        'originalTitle', 'overview', 'popularity', 'posterPath', 'production_companies',\n",
    "        'production_countries', 'release_date', 'revenue', 'runtime', 'spoken_languages', 'status',\n",
    "        'tagline', 'title', 'video', 'vote_average', 'vote_count'\n",
    "    ]\n",
    "    \n",
    "    # 默认值设置\n",
    "    default_values = {\n",
    "        'popularity': 0,\n",
    "        'runtime': 0,\n",
    "        'vote_average': 0,\n",
    "        'vote_count': 0\n",
    "    }\n",
    "    \n",
    "    # 提取现有 movie 信息，并初始化未提供的列为空值或默认值\n",
    "    movie_data = {col: movie_info.get(col, default_values.get(col, None)) for col in movie_columns}\n",
    "    \n",
    "    # 转换为 DataFrame\n",
    "    movie_df = pd.DataFrame([movie_data])\n",
    "\n",
    "    # 获取电影的 id\n",
    "    movie_id = movie_info.get('id', None)\n",
    "    \n",
    "    # 提取关键词信息，添加电影 id 并转换为 DataFrame\n",
    "    keywords = data.get('keywords', [])\n",
    "    # for keyword in keywords:\n",
    "    #     keyword['movie_id'] = movie_id\n",
    "    # keywords_df = pd.DataFrame(keywords)\n",
    "    keywords_str = json.dumps(keywords)  # 将关键词列表转换为字符串格式\n",
    "    keywords_df = pd.DataFrame([{\n",
    "            'id': movie_id,\n",
    "            'keywords':keywords_str,\n",
    "        }])\n",
    "\n",
    "    # 创建一个 credits DataFrame\n",
    "    credits_df = pd.DataFrame([{\n",
    "        'id': movie_id,\n",
    "        'cast': json.dumps(data.get('actors', [])),\n",
    "        'crew': []\n",
    "    }])\n",
    "\n",
    "    return actors_df, movie_df, keywords_df, credits_df\n",
    "\n",
    "# 示例 JSON 数据\n",
    "json_data = '{\"actors\":[{\"id\":1,\"gender\":\"Female\",\"name\":\"George Lucas\"}],\"movie\":{\"id\":469173,\"adult\":true,\"budget\":0,\"homepage\":\"123\",\"originalLanguage\":\"en\",\"originalTitle\":\"\",\"popularity\":0,\"posterPath\":\"123\",\"releaseDate\":\"\",\"revenue\":0,\"runtime\":0,\"status\":\"\",\"tagline\":\"\",\"title\":\"ceshi\",\"voteAverage\":0,\"voteCount\":0,\"overview\":\"ceshidianying\",\"seenCount\":0,\"companyName\":\"ceshi\",\"countryName\":\"cs\"},\"genreHubs\":[{\"id\":12,\"name\":\"Adventure\"}],\"keywords\":[{\"id\":931,\"name\":\"jealousy\"}]}'\n",
    "#json_data = extract_json_from_string(json_data)\n",
    "# 将 JSON 数据转换为 DataFrame\n",
    "actors_df, movie_df, keywords_df, credits_df = json_to_dataframes(json_data)\n",
    "# print(\"Keywords DataFrame:\")\n",
    "# print(keywords_df, \"\\n\")\n",
    "clean1(credits_df,movie_df,keywords_df)\n",
    "# # 打印处理后的 DataFrame\n",
    "# print(\"Actors DataFrame:\")\n",
    "# print(actors_df, \"\\n\")\n",
    "\n",
    "# print(\"Movie DataFrame:\")\n",
    "# print(movie_df, \"\\n\")\n",
    "\n",
    "# print(\"Keywords DataFrame:\")\n",
    "# print(keywords_df, \"\\n\")\n",
    "\n",
    "# print(\"Credits DataFrame:\")\n",
    "# print(credits_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def main():\n",
    "    # 设置参数解析器\n",
    "    parser = argparse.ArgumentParser(description=\"处理 JSON 数据并将其转换为多个 Pandas DataFrame。\")\n",
    "    \n",
    "    # 添加参数，用于接收 JSON 数据\n",
    "    parser.add_argument('json_data', type=str, help='输入的 JSON 数据字符串')\n",
    "    \n",
    "    # 解析命令行参数\n",
    "    args = parser.parse_args()\n",
    "    \n",
    "    # 将 JSON 数据转换为 DataFrame\n",
    "    actors_df, movie_df, keywords_df, credits_df = json_to_dataframes(args.json_data)\n",
    "    \n",
    "    # 调用 clean1 函数\n",
    "    clean1(credits_df, movie_df, keywords_df)\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pytorch",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.17"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
